﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using MISAPP.Common;
using System.Data.Common;

namespace MISAPP.Classify.DataAccess
{
    public class DA_WMG001 : DA_Matrix
    {

        public DA_WMG001()
        {
            tableName = "WMG.dbo.WMG001";
        }

        public override string[] GetDistinctField()
        {
            return new string[]{"年月","分行別","存款類別"
                            ,"版塊別","客群別"
                            ,"區別","群組"
                            ,"幣別","利率型態"};
        }

        public DataTable GetSummaryData()
        {
            string sql;
            GetCriteriaString();            
            sql = string.Format(getSummaryStatement()
                    , CriteriaString
                );

            AddParameter(sql);
            
            return getData();             
        }

        protected string getSummaryStatement()
        {
            return @"SELECT 
                        a.年月
                        , a.版塊別
                        , a.客群別
                        , a.分行別
                        , a.區別
                        , a.存款類別
                        , a.幣別
                        , a.利率型態
                        , a.ORG_TERM
                        , CONVERT(NVARCHAR,CAST(a.BAL AS MONEY),1) BAL                        
                        , CONVERT(NVARCHAR,CAST(a.AVG_BAL AS MONEY),1) AVG_BAL
                        , a.AVG_RATE
                        , a.FTP_RATE
                        , a.FTP_NET_RATE
                        , a.群組
                        , dbo.GetInterestExpense(a.AVG_BAL,a.AVG_RATE,a.幣別,a.存款類別,a.年月) AS 利息支出
                        , dbo.GetFtpRate(a.幣別,a.存款類別,a.AVG_BAL,a.FTP_RATE,a.年月) AS FTP息
                        , dbo.GetFtpRate(a.幣別,a.存款類別,a.AVG_BAL,a.FTP_RATE,a.年月) -
                          dbo.GetInterestExpense(a.AVG_BAL,a.AVG_RATE,a.幣別,a.存款類別,a.年月) AS 利差
                        FROM WMG.dbo.wmg001 a
                        WHERE 1=1 
                        {0}
 ORDER BY  a.年月";
    
        }

    }
}
